﻿Imports DataAccess

Public Class BSAttendance



    Public Sub New(ByVal serverName As String)
        DAAtendance.ConfigServerName(serverName)
    End Sub

    Public Function GetAttendanceList(ByVal companyNo As String, ByVal dDate As Date,
                                      ByRef contractHr As Double,
                                      ByRef workingHr As Double,
                                      ByRef overtimeHr As Double) As DataTable

        ' get all employees if existed department.
        Dim dtEmployees As DataTable = DAAtendance.GetEmployees(companyNo)

        ' tao mot datatable la cai return lai :) dem lam datasource luon. 
        Dim dtResult As DataTable = New DataTable()
        dtResult.Columns.Add("Company")
        dtResult.Columns.Add("Department")
        dtResult.Columns.Add("Employee No.")
        dtResult.Columns.Add("Employee Name")
        dtResult.Columns.Add("Contract Hours")
        dtResult.Columns.Add("Working Hours")
        dtResult.Columns.Add("Overtime Hours")
        ' dtResult.Columns.Add("Remarks")


        ' duyet tat ca employee tinh de tinh thoi gian
        For Each row As DataRow In dtEmployees.Rows
            ' create new row in dtResult
            Dim dataRow As DataRow = dtResult.NewRow()
            ' get day from and day to of a month
            Dim dayFrom As Date
            Dim dayTo As Date
            GetPeriodTimeOfMonth(companyNo, dDate.Day, dDate.Month, dDate.Year, dayFrom, dayTo)
            '-------------------------------------------set Company, department, employee name, remarks-------------------------------------------------------------------------
            dataRow.Item("Company") = row.Item("COMPANY_NAME")
            dataRow.Item("Department") = row.Item("DEPARTMENT_NAME")
            dataRow.Item("Employee Name") = row.Item("EMPLOYEE_NAME")
            dataRow.Item("Employee No.") = row.Item("EMPLOYEE_NO")
            '------------------------------------------ Tinh contract time cua 1 employee-----------------------------------------
            ' get timetable of employee: No, start, end
            Dim dtTimetable As DataTable = DAAtendance.GetTimetable(row.Item("EMPLOYEE_NO"))

            ' contract time trong 1 ngay chua tru rest time
            Dim contractTime As Integer = SubtractHourToHour(dtTimetable.Rows.Item(0).Item("WORK_TO"), dtTimetable.Rows.Item(0).Item("WORK_FROM")) ' dung ham subtract

            ' get all rest time in timetable: rest from, rest to
            Dim dtRestTime As DataTable = DAAtendance.GetRestTime(dtTimetable.Rows.Item(0).Item("TIME_TABLE_NO"))

            For Each dr As DataRow In dtRestTime.Rows
                If dr.Item("REST_FROM") > dtTimetable.Rows.Item(0).Item("WORK_FROM") And dr.Item("REST_TO") < dtTimetable.Rows.Item(0).Item("WORK_TO") Then

                    contractTime = SubtractHourToHour(contractTime, SubtractHourToHour(dr.Item("REST_TO"), dr.Item("REST_FROM")))

                End If
            Next

            ' get holiday cua employee trong thang hien tai 
            Dim dtHoliday As DataTable = DAAtendance.GetHolidays(companyNo, row.Item("DEPARTMENT_NO"), row.Item("SUP_DEPARTMENT_NO"))
            Dim iContractAllDays As Integer = 0
            Dim dContractAllDays As Double = 0
            'MsgBox(MultiplicationHours(daysOfMonthly(dayFrom, dayTo), contractTime))
            If dtHoliday.Rows.Count > 0 Then
                Dim sumOfHoliday As Integer = dtHoliday.Rows.Count
                ' contract time all days in month
                iContractAllDays = MultiplicationHoursInteger(contractTime, (daysOfMonthly(dayFrom, dayTo) - sumOfHoliday))
                dContractAllDays = MultiplicationHoursDouble(contractTime, (daysOfMonthly(dayFrom, dayTo) - sumOfHoliday))
            Else
                iContractAllDays = MultiplicationHoursInteger(contractTime, daysOfMonthly(dayFrom, dayTo))
                dContractAllDays = MultiplicationHoursDouble(contractTime, daysOfMonthly(dayFrom, dayTo))
            End If

            ' set contract time
            dataRow.Item("Contract Hours") = iContractAllDays
            ' add sum contract hours
            contractHr += dContractAllDays

            '----------------------------------------------Tinh working time, over time cua 1 employee-------------------------------------------------------------------------------- 

            'Get work data 
            Dim dtWorkData As DataTable = DAAtendance.GetWorkData(companyNo, row.Item("EMPLOYEE_NO"), dayFrom.ToString("yyyyMMdd"), dayTo.ToString("yyyyMMdd"))

            Dim workingTime As Integer = 0
            Dim overtime As Integer = 0
            ' tinh gio lam viec va overtime 
            For Each r As DataRow In dtWorkData.Rows
                '  workingTime = AddHours(workingTime, r.Item("WORKING_TIME") / 60)
                workingTime += r.Item("WORKING_TIME")
                If Not IsDBNull(r.Item("OVER_TIME")) Then
                    'overtime = AddHours(overtime, r.Item("OVER_TIME") / 60)
                    overtime += r.Item("OVER_TIME")
                End If
            Next
            ' set working time 
            dataRow.Item("Working Hours") = workingTime / 60
            ' add sum working time
            workingHr += workingTime
            ' set overtime 
            dataRow.Item("Overtime Hours") = overtime / 60
            ' add sum working time
            overtimeHr += overtimeHr
            dtResult.Rows.Add(dataRow)
        Next
        workingHr = workingHr / 60
        overtimeHr = overtimeHr / 60

        Return dtResult

    End Function

    ''' <summary>
    ''' lay year trong database va check ney year hien tai 
    ''' chua co thi them vao co roi thi thoi
    ''' </summary>
    ''' <param name="year"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetYear() As DataTable
        Dim dt As DataTable = DAAtendance.GetYear()
        Dim currentYear As Integer = Date.Now.Year
        If dt.Rows.Count = 0 Then
            dt = New DataTable()
            dt.Columns.Add("Year")
            dt.Rows.Add(currentYear)
        Else
            Dim i As Integer
            For i = 0 To dt.Rows.Count - 1
                If dt.Rows.Item(i).Item(0) = currentYear Then
                    Exit For
                End If
            Next
            If i <= dt.Rows.Count Then
                dt.Rows.Add(currentYear)
            End If

        End If
        Return dt
    End Function

    ''' <summary>
    ''' get cut off date cua 1 thang
    ''' </summary>
    ''' <param name="companyNo"></param>
    ''' <param name="month"></param>
    ''' <param name="year"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Shared Function GetCutOffDate(ByVal companyNo As String, ByVal month As String, ByVal year As String) As Integer
        Dim dt As DataTable = daAtendance.GetCutOffDay(companyNo)
        year &= month
        Dim yearMonth As ULong = UInt64.Parse(year)
        For Each row As DataRow In dt.Rows
            If yearMonth > UInt64.Parse(row("EXPIRATION_FROM").ToString()) And yearMonth <= UInt64.Parse(row("EXPIRATION_TO").ToString()) Then
                Return Int32.Parse(row("CUT_OFF_DATE"))
            End If
        Next
    End Function

    ''' <summary>
    ''' tinh ngay bat dau ket thuc cua 1 thang
    ''' </summary>
    ''' <param name="companyNo"></param>
    ''' <param name="month"></param>
    ''' <param name="year"></param>
    ''' <param name="dayFrom"></param>
    ''' <param name="dayTo"></param>
    ''' <remarks></remarks>
    Shared Sub GetPeriodTimeOfMonth(ByVal companyNo As String, ByVal day As Integer, ByVal month As Integer, ByVal year As Integer, ByRef dayFrom As Date, ByRef dayTo As Date)
        Dim cutOffDay As Integer = GetCutOffDate(companyNo, month, year)
        Dim days As Integer = Date.DaysInMonth(year, month)

        Try
            If (cutOffDay + 1) <= days Then
                ' cut off day is not the last day of month
                If day <= (cutOffDay) Then
                    ' month is January
                    If (month - 1) < 1 Then
                        dayFrom = New Date(year - 1, 12, cutOffDay + 1)
                        dayTo = New Date(year, 1, cutOffDay)
                    Else
                        If Date.DaysInMonth(year, month - 1) < cutOffDay + 1 Then
                            dayFrom = New Date(year, month - 1, Date.DaysInMonth(year, month - 1))
                        Else
                            dayFrom = New Date(year, month - 1, cutOffDay + 1)
                        End If
                        dayTo = New Date(year, month, cutOffDay)
                    End If

                Else
                    dayFrom = New Date(year, month, cutOffDay + 1)
                    If month = 12 Then
                        dayTo = New Date(year + 1, 1, cutOffDay)
                    Else
                        ' get number of days in next month
                        Dim d As Integer = Date.DaysInMonth(year, month + 1)
                        ' check day to in month
                        If cutOffDay <= d Then
                            dayTo = New Date(year, month + 1, cutOffDay)
                        Else
                            dayTo = New Date(year, month + 1, d)
                        End If
                    End If
                End If
            Else
                ' cut off day is the last of month
                dayFrom = New Date(year, month, 1)
                ' get number of days in this month
                Dim d As Integer = Date.DaysInMonth(year, month)
                ' check day to in month
                If cutOffDay <= d Then
                    dayTo = New Date(year, month, cutOffDay)
                Else
                    dayTo = New Date(year, month, d)
                End If
                '   MsgBox(Me.month)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub



    ''' <summary>
    ''' subtract format hours from format hours. Return hours
    ''' </summary>
    ''' <param name="x"></param>
    ''' <param name="y"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Shared Function SubtractHourToHour(ByVal x As Integer, ByVal y As Integer) As Integer
        x = ((x \ 100) * 60 + (x Mod 100)) - ((y \ 100) * 60 + (y Mod 100))
        Return (x \ 60) * 100 + (x Mod 60)
    End Function

    ''' <summary>
    ''' add hours and hours return hours
    ''' </summary>
    ''' <param name="x"></param>
    ''' <param name="y"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function AddHours(ByVal x As Integer, ByVal y As Integer) As Integer
        x = ((x \ 100) * 60 + (x Mod 100)) + ((y \ 100) * 60 + (y Mod 100))
        Return (x \ 60) * 100 + (x Mod 60)
    End Function

    ''' <summary>
    ''' dung hien thi datagridview
    ''' </summary>
    ''' <param name="x"></param>
    ''' <param name="y"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function MultiplicationHoursInteger(ByVal x As Integer, ByVal y As Integer) As Integer
        x = ((x \ 100) * 60 + (x Mod 100)) * y
        Return (x \ 60) + (x Mod 60)
    End Function

    ''' <summary>
    ''' dung de tinh tong gio
    ''' </summary>
    ''' <param name="x"></param>
    ''' <param name="y"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function MultiplicationHoursDouble(ByVal x As Integer, ByVal y As Integer) As Double
        x = ((x \ 100) * 60 + (x Mod 100)) * y
        Return (x \ 60)
    End Function

    Public Function daysOfMonthly(ByVal dayf As Date,
                                  ByVal dayt As Date) As Integer
        Dim numOfDay As Integer = 0

        If dayf.Month = dayt.Month Then
            For i As Integer = dayf.Day To dayt.Day
                numOfDay += 1
            Next
        Else
            ' tinh nua thang truoc
            For i As Integer = dayf.Day To Date.DaysInMonth(dayf.Year, dayf.Month)
                numOfDay += 1
            Next
            'tinh nua thang sau
            For i As Integer = 1 To dayt.Day
                numOfDay += 1
            Next

        End If
        '   MsgBox(numOfDay)
        Return numOfDay

    End Function
End Class
